﻿using KZ.Express.Resource;
using KZ.Utilities;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace KZ.Express.DAL
{
    public class DataAccessHelper
    {
        private static SqlConnection _sqlConn;
        private static SqlTransaction _sqlTrans;

        private void OpenConnection()
        {
            try
            {
                if (_sqlConn == null) { throw new DataAccessException(Message.BLM_ERR_CONN_NOT_INITIALIZED.ToString()); }
                if (_sqlConn.State != System.Data.ConnectionState.Open)
                {
                    _sqlConn.Open();
                }
            }
            catch (InvalidOperationException ex)
            {
                Log.LogEx(Message.BLM_ERR_CONN_DATASOURCE_OR_SERVER_NOT_EXIST_OR_CONN_ALREADY_OPEN.ToString(), Log.LogType.Error, ex);
                throw new DataAccessException(Message.BLM_ERR_CONN_SERVER_NOT_CONNECT.ToString());
            }
            catch (SqlException ex)
            {
                Log.LogEx(Message.BLM_ERR_CONN_USER_OR_PASSWORD_INCORRECT.ToString(), Log.LogType.Error, ex);
                throw new DataAccessException(Message.BLM_ERR_CONN_SERVER_NOT_CONNECT.ToString());
            }
            catch (ConfigurationErrorsException ex)
            {
                Log.LogEx(Message.BLM_ERR_CONN_2_ENTRY_SAMENAME_LOCALDBINSTANCE.ToString(), Log.LogType.Error, ex);
                throw new DataAccessException(Message.BLM_ERR_CONN_CONFIG_FILE_INCORRECT.ToString());
            }
            catch (DataAccessException ex)
            {
                Log.LogEx(ex.Message, Log.LogType.Error, ex);
                throw new DataAccessException(Message.BLM_ERR_CONN_SERVER_NOT_CONNECT.ToString());
            }
        }

        private void CloseConnection()
        {
            try
            {
                if (_sqlConn == null) { throw new DataAccessException(Message.BLM_ERR_CONN_NOT_INITIALIZED.ToString()); }
                if (_sqlConn.State != System.Data.ConnectionState.Closed)
                {
                    _sqlConn.Close();
                }
            }
            catch (SqlException ex)
            {
                Log.LogEx(Message.BLM_ERR_CONN_USER_OR_PASSWORD_INCORRECT.ToString(), Log.LogType.Error, ex);
                throw new DataAccessException(Message.BLM_ERR_CONN_SERVER_NOT_CONNECT.ToString());
            }
            catch (DataAccessException ex)
            {
                Log.LogEx(ex.Message, Log.LogType.Error, ex);
                throw new DataAccessException(Message.BLM_ERR_CONN_SERVER_NOT_CONNECT.ToString());
            }
        }

        public DataAccessHelper()
        {
            _sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["Default"].ConnectionString);
        }

        public void BeginTransaction()
        {
            try
            {
                if (_sqlConn == null) { throw new DataAccessException(Message.BLM_ERR_CONN_NOT_INITIALIZED.ToString()); }
                if (_sqlConn.State != ConnectionState.Open) { _sqlConn.Open(); }
                if (_sqlTrans == null)
                    _sqlTrans = _sqlConn.BeginTransaction(System.Data.IsolationLevel.Unspecified);
            }
            catch (DataAccessException ex)
            {
                Log.LogEx(ex.Message, Log.LogType.Error, ex);
                throw new DataAccessException(Message.BLM_ERR_CONN_SERVER_NOT_CONNECT.ToString());
            }
        }

        public void CommitTransaction()
        {
            try
            {
                if (_sqlConn == null) { throw new DataAccessException(Message.BLM_ERR_CONN_NOT_INITIALIZED.ToString()); }
                _sqlTrans.Commit();
                _sqlConn.Close();
            }
            catch (DataAccessException ex)
            {
                Log.LogEx(ex.Message, Log.LogType.Error, ex);
                throw new DataAccessException(Message.BLM_ERR_CONN_SERVER_NOT_CONNECT.ToString());
            }
            finally
            {
                _sqlTrans.Dispose();
                _sqlTrans = null;
            }
        }

        public void RollbackTransaction()
        {
            try
            {
                if (_sqlConn == null) { throw new DataAccessException(Message.BLM_ERR_CONN_NOT_INITIALIZED.ToString()); }
                _sqlTrans.Rollback();
                _sqlTrans.Dispose();
                _sqlConn.Close();
            }
            catch (DataAccessException ex)
            {
                Log.LogEx(ex.Message, Log.LogType.Error, ex);
                throw new DataAccessException(Message.BLM_ERR_CONN_SERVER_NOT_CONNECT.ToString());
            }
            finally
            {
                _sqlTrans.Dispose();
            }
        }

        public int ExecuteNonQuery(string query, params SqlParameter[] parameters)
        {
            SqlCommand sqlCmd = new SqlCommand();
            try
            {
                //// open connection if transaction not exist
                if (_sqlTrans == null) { _sqlConn.Open(); }

                sqlCmd.Connection = _sqlConn;
                sqlCmd.Transaction = _sqlTrans ?? null;
                sqlCmd.CommandText = query;
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.Parameters.AddRange(parameters);
                sqlCmd.ExecuteNonQuery();

                int value = Utility.IntParse(parameters.First().Value);
                if (value == -1) throw new DataAccessException(Message.BLM_ERR_CODE_EXISTS.ToString());

                return value;
            }
            catch (SqlException ex)
            {
                Log.LogEx(query, Log.LogType.Fatal, ex);
                throw new DataAccessException();
            }
            finally
            {
                sqlCmd.Dispose();
                if (_sqlTrans == null) { _sqlConn.Close(); }
            }
        }

        public DataTable ExecuteQuery(string query, params SqlParameter[] parameters)
        {
            SqlCommand sqlCmd = new SqlCommand();
            SqlDataAdapter sqlAdapt = new SqlDataAdapter();
            DataTable dt = new DataTable();
            try
            {
                //// open connection if transaction not exist
                if (_sqlTrans == null) { _sqlConn.Open(); }

                sqlCmd.Connection = _sqlConn;
                sqlCmd.Transaction = _sqlTrans ?? null;
                sqlCmd.CommandText = query;
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
                sqlCmd.Parameters.AddRange(parameters.ToArray());

                sqlAdapt.SelectCommand = sqlCmd;
                sqlAdapt.Fill(dt);

                return dt;
            }
            catch (SqlException ex)
            {
                Log.LogEx(query, Log.LogType.Fatal, ex);
                throw new DataAccessException();
            }
            finally
            {
                sqlCmd.Dispose();
                sqlAdapt.Dispose();
                if (_sqlTrans == null) { _sqlConn.Close(); }
            }
        }
    }
}